How to: Create a delete query.
Solution:
Create a new query in Design view, add the desired fields to the design grid, and select the 'Query' menu and select 'Delete'.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Click 'New'. (The New Query dialog box appears.)
4) Select 'Design View' from the list box.
5) Click 'OK'. (The query opens in Design view, and the Show Table dialog box appears.)
6) Do one of the following:
a) Click the 'Tables' tab to display a list of tables only.
b) Click the 'Queries' tab to display a list of queries only.
c) Click the 'Both' tab to display a list of both tables and queries.
7) Select the table(s) and/or querie(s) to include in the query from the list box.
NOTE: To select more than one adjacent table or query, press and hold down SHIFT while selecting the tables or queries. To select more than one non-adjacent table or query, press and hold down CTRL while selecting the tables or queries.
8) Click 'Add'.
9) Click 'Close' when all of the desired tables and/or queries have been added.
10) Select the 'Query' menu and select 'Delete'.
11) Do one of the following to add fields that are to be deleted from the tables to the query design grid:
a) To delete records from a single table or from multiple tables with a one-to-one relationship:
NOTE: In the query Design view, at the top of the window, if one end of a join between two tables has the infinity symbol, it is a one-to-many relationship. Otherwise, it is a one-to-one relationship.
1] From the field list for the table from which to delete records, drag the asterisk (*) to the query design grid. ('From' appears in the 'Delete' cell in the query design grid.)
2] Drag fields on which to set criteria to the query design grid. ('Where' appears in the 'Delete' cell in the query design grid.)
b) To delete records from multiple tables with a one-to-many relationship:
NOTE: In the query Design view, at the top of the window, if one end of a join between two tables has the infinity symbol, it is a one-to-many relationship. Otherwise, it is a one-to-one relationship.
1] From the field list for the table with the 'many' side of the relationship (contains records related to the primary table), drag the asterisk (*) to the query design grid. ('From' appears in the 'Delete' cell in the query design grid.)
2] From the field list for the primary table (with the 'one' side of the relationship), drag the fields on which to set criteria to the query design grid. ('Where' appears in the 'Delete' cell in the query design grid.)
12) Type the desired criteria in the 'Criteria' row for the fields dragged to the query design grid in step 11)a)2] or step 11)b)2].
13) To preview the changes to the table BEFORE deleting any data, select the 'View' menu and select 'Datasheet'.
14) Do one of the following to run the query and delete the records:
NOTE: To stop a query after it has started, press CTRL+BREAK.
a) To delete the records from the single table for a table or one-to-one relationship:
1] Make sure Query Design view is open.
NOTE: If Datasheet view is open, select the 'View' menu and select 'Query Design'.
2] Select the 'Query' menu and select 'Run'. (The records are deleted.)
b) To delete the records from the ''many'' table for a one-to-many relationship:
1] Make sure Query Design view is open.
NOTE: If Datasheet view is open, select the 'View' menu and select 'Query Design'.
2] Select the 'Query' menu and select 'Run'.
3] Select the field list for each related table.
4] Press DELETE.
5] Repeat steps 14)b)3] and 14)b)4] for each related table. (When finished, all of the records from the 'many' table will be deleted. Only the primary table and the fields on which to set criteria remain in the query.)
6] Select the 'Query' menu and select 'Run'. (The records from the 'one' table are deleted.)
15) To save the query:
a) Select the 'File' menu and select 'Save'. (The Save As dialog box appears.)
b) Type a name for the delete query in the 'Query Name' box.
c) Click 'OK'.
16) Select the 'File' menu and select 'Close' to close the Query Design view.
